package cn.tworice.backup.executor.impl;

import cn.tworice.backup.executor.BackupExecutor;
import cn.tworice.backup.executor.DatabaseParam;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;


public class MySQLBackupExecutor implements BackupExecutor {

    private Connection connection = null;

    private Statement statement = null;

    private DatabaseParam dataSourceProperties;


    public MySQLBackupExecutor(DatabaseParam params){
        this.dataSourceProperties = params;
    }

    public MySQLBackupExecutor() {}

    /**
     * @param path 要备份sql的目录路径
     */
    @Override
    public void backup(StringBuilder path){
        try{
            this.connectSQL();
            this.exportAllTableSchemas(path);
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 连接数据库创建statement对象
     */
    public void connectSQL() throws SQLException {
        try {
            Class.forName(dataSourceProperties.getDriver()).newInstance();
            connection = DriverManager.getConnection(dataSourceProperties.getUrl(),
                    dataSourceProperties.getUsername(),
                    dataSourceProperties.getPassword());
            statement = connection.createStatement();
        } catch (Exception e) {
            connection.close();
            throw new RuntimeException("创建数据库连接异常："+e.getMessage());
        }
    }

    public void exportAllTableSchemas(StringBuilder path) throws SQLException {
        String dateString = this.getDateString();
        path.append(File.separator).append(dataSourceProperties.getDatabaseName()).append("_").append(dateString).append(".sql");
        File file = new File(path.toString());
        file.getParentFile().mkdirs(); // 自动创建多级目录
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(file, true))) {
            // 获取所有的表
            ResultSet resultSet = statement.executeQuery("SHOW TABLES FROM " + dataSourceProperties.getDatabaseName());
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                writer.write("/* 表名：" + tableName + " */");
                writer.newLine();
                Statement innerStatement = connection.createStatement();  // 创建新的Statement
                ResultSet tableResult = innerStatement.executeQuery("SHOW CREATE TABLE " + dataSourceProperties.getDatabaseName() + "." + tableName);

                if (tableResult.next()) {
                    String createTableSQL = tableResult.getString(2);
                    writer.write(createTableSQL + ";");
                    writer.newLine();
                    this.exportDataToInsert(tableName, writer);
                    writer.newLine();
                }

                tableResult.close();
                innerStatement.close();  // 关闭内部Statement
            }
        } catch (Exception e) {
            connection.close();
            throw new RuntimeException(e);
        }
    }

    private String getDateString(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
        return sdf.format(new Date());
    }

    /**
     * 生成INSERT语句
     * @param tableName 表名
     * @param writer BufferedWriter对象，用于写入文件
     */
    private void exportDataToInsert(String tableName,BufferedWriter writer){
        try (
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM " + tableName);) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {
                StringBuilder insertStatement = new StringBuilder();
                insertStatement.append("INSERT INTO `").append(tableName).append("` VALUES (");

                for (int i = 1; i <= columnCount; i++) {
                    Object value = resultSet.getObject(i);

                    if (value != null) {
                        insertStatement.append("'").append(value).append("'");
                    } else {
                        insertStatement.append("NULL");
                    }

                    if (i < columnCount) {
                        insertStatement.append(", ");
                    }
                }

                insertStatement.append(");");
                writer.write(insertStatement.toString());
                writer.newLine();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}